<?php
defined ( 'SYSPATH' ) or die ( 'No direct access allowed.' );
/**
 *
 * @author lee
 * 采购报表       
 */

class Controller_Report_Purchase extends Controller_Admin {

   public function before()
    {
    	try {
    		  ACL::Required('purchase report');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }


	public function action_index()
	{
		$this->title=__("采购订单明细报表");
		$view = View::factory('report/purchase_detail');
		$this->response->body($view);
	}
/*
*根据条件加载table
*/
	public function action_load_table()
	{
		if($_GET['way']==1)
			$table = "<table id='' class='table table-bordered ' style='border-collapse:collapse';>
								<tr style=' background-color:#4EA392;color:#fff;'>
									<th>序号</th>
									<th>SKU</th>
									<th>中文名称</th>
									<th>规格</th>
									<th>储位</th>
									<th>数量</th>
									<th>未税金额</th>
									<th>税额</th>
									<th>金额</th>
									<th>运费</th>
									<th>成本合计</th>
									<th>到货量</th>
									<th>质检量</th>
									<th>入库量</th>
									<th>未交量</th>
									<th>超交量</th>
							<tbody id ='contents' style='display:none'>
					 		</tr>
						</table>";
		elseif($_GET['way']==2)
			$table = "<table id='' class='table table-bordered' style='border-collapse:collapse'>
			<thead>
								<tr style=' background-color:#4EA392;color:#fff;'>
									<th>序号</th>
									<th>采购员</th>
									<th>数量</th>
									<th>未税金额</th>
									<th>税额</th>
									<th>金额</th>
									<th>运费</th>
									<th>成本合计</th>
					 		</tr></thead>
							<tbody id ='contents' style='display:none'>
							</tbody>
						</table>";
		elseif($_GET['way']==3)
			$table = "<table id='' class='table table-bordered' style='border-collapse:collapse'>
								<thead><tr style=' background-color:#4EA392;color:#fff;'>
									<th>序号</th>
									<th>供应商</th>
									<th>数量</th>
									<th>未税金额</th>
									<th>税额</th>
									<th>金额</th>
									<th>运费</th>
									<th>成本合计</th>
									<th>到货量</th>
									<th>质检量</th>
									<th>入库量</th>
									<th>未交量</th>
									<th>超交量</th>
					 		</tr></thead>
							<tbody id ='contents' style='display:none'>
							</tbody>
						</table>";
		else
			$table = "<table id='' class='table table-bordered' style='border-collapse:collapse'>
								<thead><tr style=' background-color:#4EA392;color:#fff;'>
									<th>序号</th>
									<th>SKU</th>
									<th>中文名称</th>
									<th>规格</th>
									<th>储位</th>
									<th>供应商</th>
									<th>数量</th>
									<th>采购单价</th>
									<th>未税金额</th>
									<th>税额</th>
									<th>金额</th>
									<th>运费</th>
									<th>成本合计</th>
									<th>到货量</th>
									<th>质检量</th>
									<th>入库量</th>
									<th>未交量</th>
									<th>超交量</th>
					 		</tr></thead>
							<tbody id ='contents' style='display:none'>
							</tbody>
						</table>";
			echo $table;
	}
/*
*根据条件采购明细搜索
*/ 
	public function action_search()
	{
		$sql = $this->sql_search();
		$items_per_page=50;
 	  if(isset($_GET['page'])&&!empty($_GET['page']))
 		{
 			$offset=($_GET['page']-1)*$items_per_page;
 		}else{
 			$offset=0;
 		}
		$pagination         = Pagination::factory(array(
	      'items_per_page'  =>$items_per_page, 
	      'total_items'     => count($sql->execute('newerp')->as_array()),
	      'uri'	            => $this->request->uri(),
	  ));

		$sql      ->limit($pagination->items_per_page);
		$sql      ->offset($offset);
		$pager_links = $pagination->render();
		$totles      = $sql->execute('newerp')->as_array();//print_r($result);exit;
		if(empty($totles))
		{
			echo 1;exit;
		}else
		{
			$totles    = Reports::reduct_detail_data($totles);
		}

        //添加入库时间，付款时间，付款总金额
        foreach($totles as &$totle)
        {
            $wa_dates=DB::select('warehouse_date')->from('warehouseorders')
                ->where('purchaseorders_id','=',$totle['poid'])
                ->where('sku','=',$totle['sku'])
                ->execute('newerp')
                ->as_array();

            $totle['wa_date'] = '';
            foreach($wa_dates as $wa_date)
            {
                $totle['wa_date'] .= $totle['wa_date']?','.$wa_date['warehouse_date']:$wa_date['warehouse_date'];
            }
            $pay=DB::select('pay_date',DB::expr("sum(amount_paid) as amount_paid"))
                ->from('purchaseorder_pays')
                ->where('purchaseorder_id','=',$totle['poid'])
                ->execute('newerp')
                ->current();
            if($pay)
            {
                $totle['pay_date'] = isset($pay['pay_date'])?$pay['pay_date']:'';
                $totle['pay_money'] = isset($pay['amount_paid'])?$pay['amount_paid']:'';
            }else{
                $totle['pay_date'] = '';
                $totle['pay_money'] = '';
            }
        }
		$result=array('pager_links'=>$pager_links,'totles'=>$totles);
		$views= View::factory('report/detail');
		$views->set('current_page',isset($_GET['page'])?$_GET['page']:1);
		$views->set('per_page',$items_per_page);
  	$views->set('totles',$result['totles'])->render();
  	echo $views;	exit;
	}
/*
*根据条件采购明细搜索的方法
*/
	private function sql_search()
	{
		$sql = DB::select(array("purchaseorders.id",'poid'),array("purchaseorders.transportation_expense",'te'),
		'purchaseorders.*',"purchaseorder_details.*")->from('purchaseorders');
		$sql->join('purchaseorder_details')->on('purchaseorders.id', '=', 'purchaseorder_details.purchaseorder_id')
            ->order_by('purchaseorders.purchaseorder_date','DESC');
		if(isset($_GET['date_from']))
		{
			$sql->where('purchaseorder_date','>=',$_GET['date_from']);			
		}
		if($_GET['date_to'])
		{
			$sql->where('purchaseorder_date','<=',$_GET['date_to']);
		}else{
			$sql->where('purchaseorder_date','<=',date('Y-m-d H:i:s',time()));
		}
		if($_GET['supplier'])
		{
            //START     ----------------------------------CWY更新代码-------------------------------------
            $name = trim($_GET['supplier']);
            $supplier = Newerpclientapi::getSuppliers(array('id'),array('name' => "like '%$name%'"));
//			$supplier = DB::select('id')
//												->from('erp_provider')
//												->where('vProviderName','like',"%".trim($_GET['supplier'])."%")
//												->execute('readonlyerp')->as_array();
            //END     ----------------------------------CWY更新代码-------------------------------------
			if(!empty($supplier))
				$sql->where('supplier_id','IN',$supplier);
			else
				$sql->where('supplier_id','=',-1);
		}
		if($_GET['purchaser'])
		{
			$id = ORM::factory('user')->where('nick','=',$_GET['purchaser'])->find()->id;
			if($id)
				$sql->where('purchase_id','=',$id);
			else
				$sql->where('purchase_id','=',-1);
		}
		if($_GET['sku'])
		{
			$sql->where('sku','=',$_GET['sku']);
		}

		//付款时间查询
		if($_GET['paydate_from']!='' || $_GET['paydate_to']!=''){
		//通过付款时间查询
		$pay_array =array(0);
		$po_pays_sql = "SELECT `purchaseorder_id` FROM `purchaseorder_pays` where 1 ";
		if($_GET['paydate_from']!=''){
			$po_pays_sql .=" AND pay_date>='".$_GET['paydate_from']."'";
		}
		if($_GET['paydate_to']!=''){
			$po_pays_sql .=" AND pay_date<='".$_GET['paydate_to']."'";
		}
			$po_pays_sql .=" group by purchaseorder_id";
			$po_pays = DB::query(Database::SELECT,$po_pays_sql)->execute('newerp')->as_array();
		foreach($po_pays as $po_pay){
			$pay_array[] =$po_pay['purchaseorder_id'];
		}
		$sql->where('purchaseorders.id','IN',$pay_array);
		}	

		$sql->where('purchaseorder_details.status','<',100);
		return $sql;
	}
/*
*根据条件采购明细搜索总计
*/
	public function action_search_totals()
	{
		$sql = $this->sql_search();
//		$count = $sql->execute('ewerp')->as_array();
//		if($count==0){
//			echo '';exit;
//		}else{
			echo count($sql->execute('newerp')->as_array());exit;
		//}
	}
/*
*根据条件采购明细下载
*/
	public function action_download()
	{
 		set_time_limit(0);
 		ini_set('memory_limit','1024M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("ROMWE")
 		->setLastModifiedBy("ROMWE")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', '采购日期');
 		$objActSheet->setCellValue('B1', '采购明细单号');
 		$objActSheet->setCellValue('C1', '采购单号');
 		$objActSheet->setCellValue('D1', 'SKU');
 		$objActSheet->setCellValue('E1', '中文名称');

 		$objActSheet->setCellValue('F1', '供应商');
 		$objActSheet->setCellValue('G1', '数量');
// 		$objActSheet->setCellValue('H1', '单价');
// 		$objActSheet->setCellValue('I1', '未税金额');
// 		$objActSheet->setCellValue('J1', '税额');
// 		$objActSheet->setCellValue('K1', '金额');
// 		$objActSheet->setCellValue('L1', '运费');
// 		$objActSheet->setCellValue('M1', '成本合计');

 		$objActSheet->setCellValue('H1', '采购员');
 		$objActSheet->setCellValue('I1', '到货量');
 		$objActSheet->setCellValue('J1', '质检量');
 		$objActSheet->setCellValue('K1', '入库量');
        $objActSheet->setCellValue('L1', '入库时间');
//        $objActSheet->setCellValue('S1', '付款时间');
//        $objActSheet->setCellValue('T1', '付款金额');
		$objActSheet->setCellValue('M1', '未交量');
		$objActSheet->setCellValue('N1', '超交量');
 		$objActSheet->setCellValue('O1', '订单物品状态');
        $objActSheet->setCellValue('P1', '到货日期');
//		$objActSheet->setCellValue('X1', '付款方式');
		//$objActSheet->setCellValue('Y1', '付款时间');

 		$time=date('Y-m-d H:i:s');
 		if(isset($_GET['rem'])&&$_GET['rem']==1){
			$datas = $this->select_total_all(); 		
 		}else{
			$datas = $this->select_total();
 		}

        //添加入库时间，付款时间，付款总金额
        $pu_ids = $skus = array();
        foreach($datas as $data)
        {
            if($skus)
            {
                if(!in_array($data['sku'],$skus))
                {
                    $skus[] = $data['sku'];
                }
            }else{
                $skus[] = $data['sku'];
            }
            if($pu_ids)
            {
                if(!in_array($data['poid'],$pu_ids))
                {
                    $pu_ids[]=$data['poid'];
                }
            }else{
                $pu_ids[]=$data['poid'];
            }
        }

        if($pu_ids)
        {
            $pay=DB::select_array(array('purchaseorder_id',DB::expr("sum(amount_paid) as amount_paid")))
                    ->from('purchaseorder_pays')
                    ->where('purchaseorder_id','in',$pu_ids)
                    ->group_by('purchaseorder_id')
                    ->execute('newerp')
                    ->as_array('purchaseorder_id','amount_paid');
        }

        $wa_ids = DB::select('id')->from('warehouseorders')
            ->where('purchaseorders_id','in',$pu_ids)
            ->execute('newerp')
            ->as_array(NULL,'id');
        if($wa_ids&&$skus)
        {
            $infos=DB::select('warehouse_date','purchaseorders_id','sku')->from('warehouseorders')
                ->where('id','in',$wa_ids)
                ->where('sku','in',$skus)
                ->execute('newerp')
                ->as_array();
            $results = array();
            foreach($infos as $info)
            {
                if(isset($results[$info['purchaseorders_id']][$info['sku']]))
                {
                    $results[$info['purchaseorders_id']][$info['sku']] .= ','.$info['warehouse_date'];
                }else{
                    $results[$info['purchaseorders_id']][$info['sku']] = $info['warehouse_date'];
                }
            }
        }

        foreach($datas as &$data)
        {
            if(isset($pay[$data['purchaseorder_id']]))
            {
                $data['pay_money'] = $pay[$data['purchaseorder_id']];
            }else{
                $data['pay_money'] = 0;
            }

            if(isset($results[$data['purchaseorder_id']][$data['sku']]))
            {
                $data['wa_date'] = $results[$data['purchaseorder_id']][$data['sku']];
            }else{
                $data['wa_date'] = 0;
            }
        }
		//Erpuser::nick_by_id()
 		for($i=0;$i<count($datas);$i++)
 		{
	 		$key=$i+2;
	 		$objActSheet->setCellValue('A'.$key, $datas[$i]['purchaseorder_date']);
	 		$objActSheet->setCellValue('B'.$key, $datas[$i]['id']);
			$objActSheet->setCellValue('C'.$key, $datas[$i]['poid']);
	 		$objActSheet->setCellValue('D'.$key, $datas[$i]['sku']);
	 		$objActSheet->setCellValue('E'.$key, $datas[$i]['sku_name']);

	 		$objActSheet->setCellValue('F'.$key, $datas[$i]['supplier']);
	 		$objActSheet->setCellValue('G'.$key, $datas[$i]['quantity']);
//	 		$objActSheet->setCellValue('H'.$key, $datas[$i]['single_price']);
//	 		$objActSheet->setCellValue('I'.$key, $datas[$i]['no_tax_money']);
//	 		$objActSheet->setCellValue('J'.$key, $datas[$i]['tax_money']);
//	 		$objActSheet->setCellValue('K'.$key, $datas[$i]['money']);
//	 		$objActSheet->setCellValue('L'.$key, $datas[$i]['transportation_expense']);
//	 		$objActSheet->setCellValue('M'.$key, $datas[$i]['total']);

	 		$objActSheet->setCellValue('H'.$key, $datas[$i]['purchase_id']);
	 		$objActSheet->setCellValue('I'.$key, $datas[$i]['recieve_quantity']);
	 		$objActSheet->setCellValue('J'.$key, $datas[$i]['check_quantity']);
	 		$objActSheet->setCellValue('K'.$key, $datas[$i]['ware_quantity']);
            $objActSheet->setCellValue('L'.$key, $datas[$i]['wa_date']);
//            $objActSheet->setCellValue('S'.$key, $datas[$i]['pay_time']);
//            $objActSheet->setCellValue('T'.$key, $datas[$i]['pay_money']);
	 		$objActSheet->setCellValue('M'.$key, $datas[$i]['no_recieve']);
	 		$objActSheet->setCellValue('N'.$key, $datas[$i]['extra_recieve_quantity']);
	 		$objActSheet->setCellValue('O'.$key, $datas[$i]['status']);
            $objActSheet->setCellValue('P'.$key, $datas[$i]['arrive_date']);
//	 		$objActSheet->setCellValue('X'.$key, $datas[$i]['pay_way']);
	 		//$objActSheet->setCellValue('Y'.$key, $datas[$i]['pay_time']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('采购明细报表');
 		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'采购明细报表.xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 		$objWriter->save('php://output');
 		exit;
	}
/*
*根据条件采购报表搜索sql以及采购明细结果筛选
*/
	private function select_total()
	{
		$condition =array();
		$condition['date_from'] = isset($_GET['date_from'])?trim($_GET['date_from']):"";
		$condition['date_to']   = isset($_GET['date_to'])?trim($_GET['date_to']):"";
		$condition['paydate_from'] = isset($_GET['paydate_from'])?trim($_GET['paydate_from']):"";
		$condition['paydate_to']   = isset($_GET['paydate_to'])?trim($_GET['paydate_to']):"";
		$condition['supplier']  = isset($_GET['supplier'])?trim($_GET['supplier']):"";
		$condition['purchaser'] = isset($_GET['purchaser'])?trim($_GET['purchaser']):"";;
		$condition['sku']       = isset($_GET['sku'])?trim($_GET['sku']):"";
		$condition['way']       = isset($_GET['way'])?trim($_GET['way']):"";

		if(isset($_GET['way']))
		{
			if($_GET['way'])
				$data = Reports::select_totals_purchase($condition);
				return $data;
		}
		$data = Reports::select_totals($condition);//采购明细结果筛选
		return $data ;
	}
	
/*
*根据条件采购报表搜索sql以及采购明细结果筛选（包含取消的订单）
*/
	private function select_total_all()
	{
		$condition =array();
		$condition['date_from'] = isset($_GET['date_from'])?trim($_GET['date_from']):"";
		$condition['date_to']   = isset($_GET['date_to'])?trim($_GET['date_to']):"";
		$condition['paydate_from'] = isset($_GET['paydate_from'])?trim($_GET['paydate_from']):"";
		$condition['paydate_to']   = isset($_GET['paydate_to'])?trim($_GET['paydate_to']):"";
		$condition['supplier']  = isset($_GET['supplier'])?trim($_GET['supplier']):"";
		$condition['purchaser'] = isset($_GET['purchaser'])?trim($_GET['purchaser']):"";;
		$condition['sku']       = isset($_GET['sku'])?trim($_GET['sku']):"";
		$condition['way']       = isset($_GET['way'])?trim($_GET['way']):"";

		if(isset($_GET['way']))
		{
			if($_GET['way'])
				$data = Downloading::select_totals_purchase($condition);
				return $data;
		}
		$data = Downloading::select_totals($condition);//采购明细结果筛选
		return $data ;
	}	
	public function action_select()
	{
		$this->title=__("采购订单统计报表");
		$view = View::factory('report/total');
		$this->response->body($view);
		
	}
/*
*根据条件采购订单总计
*/
	public function action_search_common_totals()
	{
		$sql = $this->select_total();
		echo count(DB::query(Database::SELECT,$sql)->execute('newerp')->as_array());exit;
	}
/*
*展示通过sku、采购员等等搜索的结果
*/
	public function action_search_total()
	{
		$sql = $this->select_total();
		$items_per_page=50;
		$totle       = count(DB::query(Database::SELECT,$sql)->execute('newerp')->as_array());
		$pagination         = Pagination::factory(array(
	      'items_per_page'  => $items_per_page, 
	      'total_items'     => $totle,
	      'uri'	            => $this->request->uri(),
	  ));
    $current_page     = isset($_GET['page'])?$_GET['page']:1;
    $sql             .="  LIMIT ".(($current_page-1)*50).",50";
		$totles      = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();//print_r($result);exit;
		if(empty($totles))
		{
			echo 1;exit;
		}
		
		$totles = Reports::reset_sku_data($totles,$_GET['way']);
		$pager_links = $pagination->render();
		$result=array('pager_links'=>$pager_links,'totles'=>$totles);
	  if($_GET['way']==1)
			$views= View::factory('report/sku_total');
		else if($_GET['way']==2)
			$views= View::factory('report/purchase_total');
		else if($_GET['way']==3)
			$views= View::factory('report/supplier_total');
		else
			$views= View::factory('report/common_total');
  	$views->set('totles',$result['totles']);
		$views->set('current_page',$current_page-1);
		//$views->set('totle',$totle);
		$views->set('per_page',50);
		$views->render();
  	echo $views;	exit;
	}
/*
*通过条件下载的sql
*/
	public function action_downloads_infos()
	{
		if($_GET['way']==1)
			$this->sku_download();
		else if($_GET['way']==2)
			$this->purchase_download();
		else if($_GET['way']==3)
			$this->supplier_download();
		else 
			$this->total_download();
	}
/*
*通过sku条件下载的excel
*/
	private function sku_download()
	{
		$sql = $this->select_total();
		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("ROMWE")
 		->setLastModifiedBy("ROMWE")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', 'SKU');
 		$objActSheet->setCellValue('B1', '中文名称');
 		$objActSheet->setCellValue('C1', '规格');
 		$objActSheet->setCellValue('D1', '储位');
 		$objActSheet->setCellValue('E1', '数量');
 		$objActSheet->setCellValue('F1', '未税金额');
 		$objActSheet->setCellValue('G1', '税额');
 		$objActSheet->setCellValue('H1', '金额');
 		$objActSheet->setCellValue('I1', '运费');
 		$objActSheet->setCellValue('J1', '成本合计');
 		$objActSheet->setCellValue('K1', '到货量');
 		$objActSheet->setCellValue('L1', '质检量');
 		$objActSheet->setCellValue('M1', '入库量');
 		$objActSheet->setCellValue('N1', '未交量');
 		$objActSheet->setCellValue('O1', '超交量');
 		$time=date('Y-m-d H:i:s');
		$datas = Reports::search_total($sql);
		$datas = Reports::reset_sku_data($datas,$_GET['way']);
 		for($i=0;$i<count($datas);$i++)
 		{
	 		$key=$i+2;
	 		$objActSheet->setCellValue('A'.$key, $datas[$i]['sku']);
	 		$objActSheet->setCellValue('B'.$key, $datas[$i]['sku_name']);
			$objActSheet->setCellValue('C'.$key, $datas[$i]['sku_standard']);
	 		$objActSheet->setCellValue('D'.$key, $datas[$i]['storage_position']);
	 		$objActSheet->setCellValue('E'.$key, $datas[$i]['sum(b.quantity)']);
	 		$objActSheet->setCellValue('F'.$key, $datas[$i]['sum(b.no_tax_money)']);
	 		$objActSheet->setCellValue('G'.$key, $datas[$i]['sum(b.tax_money)']);
	 		$objActSheet->setCellValue('H'.$key, $datas[$i]['sum(b.money)']);
	 		$objActSheet->setCellValue('I'.$key, $datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('J'.$key, $datas[$i]['sum(b.no_tax_money)']+$datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('K'.$key, $datas[$i]['sum(b.recieve_quantity)']);
	 		$objActSheet->setCellValue('L'.$key, $datas[$i]['sum(b.check_quantity)']);
	 		$objActSheet->setCellValue('M'.$key, $datas[$i]['sum(b.ware_quantity)']);
	 		$objActSheet->setCellValue('N'.$key, $datas[$i]['no_recieve']);
	 		$objActSheet->setCellValue('O'.$key, $datas[$i]['extra_recieve_quantity']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('采购订单报表');
 		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'采购订单报表(sku).xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 		$objWriter->save('php://output');
 		exit;
	}
/*
*通过采购员条件下载的excel
*/
	private function purchase_download()
	{
		$sql = $this->select_total();
		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("ROMWE")
 		->setLastModifiedBy("ROMWE")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', '采购员');
 		$objActSheet->setCellValue('B1', '数量');
 		$objActSheet->setCellValue('C1', '未税金额');
 		$objActSheet->setCellValue('D1', '税额');
 		$objActSheet->setCellValue('E1', '金额');
 		$objActSheet->setCellValue('F1', '运费');
 		$objActSheet->setCellValue('G1', '成本合计');


 		$time=date('Y-m-d H:i:s');
		$datas = Reports::search_total($sql);
		$datas = Reports::reset_sku_data($datas,$_GET['way']);
 		for($i=0;$i<count($datas);$i++)
 		{
	 		$key=$i+2;
	 		$objActSheet->setCellValue('A'.$key, $datas[$i]['purchase_id']);
	 		$objActSheet->setCellValue('B'.$key, $datas[$i]['sum(b.quantity)']);
			$objActSheet->setCellValue('C'.$key, $datas[$i]['sum(b.no_tax_money)']);
	 		$objActSheet->setCellValue('D'.$key, $datas[$i]['sum(b.tax_money)']);
	 		$objActSheet->setCellValue('E'.$key, $datas[$i]['sum(b.money)']);
	 		$objActSheet->setCellValue('F'.$key, $datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('G'.$key, $datas[$i]['sum(b.transportation_expense)']+$datas[$i]['sum(b.no_tax_money)']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('采购订单报表');
 		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'采购订单报表(purchase).xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 		$objWriter->save('php://output');
 		exit;
	}
/*
*通过供应商条件下载的excel
*/
	private function supplier_download()
	{
		$sql = $this->select_total();
		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("ROMWE")
 		->setLastModifiedBy("ROMWE")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', '供应商');
 		$objActSheet->setCellValue('B1', '数量');
 		$objActSheet->setCellValue('C1', '未税金额');
 		$objActSheet->setCellValue('D1', '税额');
 		$objActSheet->setCellValue('E1', '金额');
 		$objActSheet->setCellValue('F1', '运费');
 		$objActSheet->setCellValue('G1', '成本合计');
 		$objActSheet->setCellValue('H1', '到货量');
 		$objActSheet->setCellValue('I1', '质检量');
 		$objActSheet->setCellValue('J1', '入库量');
 		$objActSheet->setCellValue('K1', '未交量');
 		$objActSheet->setCellValue('L1', '超交量');
 		$time=date('Y-m-d H:i:s');
		$datas = Reports::search_total($sql);
		$datas = Reports::reset_sku_data($datas,$_GET['way']);
 		for($i=0;$i<count($datas);$i++)
 		{
	 		$key=$i+2;
	 		$objActSheet->setCellValue('A'.$key, $datas[$i]['supplier']);
	 		$objActSheet->setCellValue('B'.$key, $datas[$i]['sum(b.quantity)']);
			$objActSheet->setCellValue('C'.$key, $datas[$i]['sum(b.no_tax_money)']);
	 		$objActSheet->setCellValue('D'.$key, $datas[$i]['sum(b.tax_money)']);
	 		$objActSheet->setCellValue('E'.$key, $datas[$i]['sum(b.money)']);
	 		$objActSheet->setCellValue('F'.$key, $datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('G'.$key, $datas[$i]['sum(b.transportation_expense)']+$datas[$i]['sum(b.no_tax_money)']);

			$objActSheet->setCellValue('H'.$key, $datas[$i]['sum(b.recieve_quantity)']);
			$objActSheet->setCellValue('I'.$key, $datas[$i]['sum(b.check_quantity)']);
	 		$objActSheet->setCellValue('J'.$key, $datas[$i]['sum(b.ware_quantity)']);
	 		$objActSheet->setCellValue('K'.$key, $datas[$i]['no_recieve']);
	 		$objActSheet->setCellValue('L'.$key, $datas[$i]['extra_recieve_quantity']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('采购订单报表');
 		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'采购订单报表(supplier).xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 		$objWriter->save('php://output');
 		exit;
	}
/*
*通过sku+供应商条件下载的excel
*/
	private function total_download()
	{
		$sql = $this->select_total();
		set_time_limit(0);
 		ini_set('memory_limit','512M');
 		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
 		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
 		$objPHPExcel      = new PHPExcel();
 		$objPHPExcel->getProperties()->setCreator("ROMWE")
 		->setLastModifiedBy("ROMWE")
 		->setTitle("Office 2003 XLSX Test Document")
 		->setSubject("Office 2003 XLSX Test Document")
 		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
 		->setKeywords("office 2003 openxml php")
 		->setCategory("Test result file");
 		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
 		$objActSheet->setCellValue('A1', 'SKU');
 		$objActSheet->setCellValue('B1', '中文名称');
 		$objActSheet->setCellValue('C1', '规格');
 		$objActSheet->setCellValue('D1', '储位');
 		$objActSheet->setCellValue('E1', '供应商');
 		$objActSheet->setCellValue('F1', '数量');
 		$objActSheet->setCellValue('G1', '采购单价');
 		$objActSheet->setCellValue('H1', '未税金额');
 		$objActSheet->setCellValue('I1', '税额');
 		$objActSheet->setCellValue('J1', '金额');
 		$objActSheet->setCellValue('K1', '运费');
 		$objActSheet->setCellValue('L1', '成本合计');
 		$objActSheet->setCellValue('M1', '到货量');
 		$objActSheet->setCellValue('N1', '质检量');
 		$objActSheet->setCellValue('O1', '入库量');
 		$objActSheet->setCellValue('P1', '未交量');
 		$objActSheet->setCellValue('Q1', '超交量');
 		$time=date('Y-m-d H:i:s');
		$datas = Reports::search_total($sql);
		$datas = Reports::reset_sku_data($datas,$_GET['way']);
 		for($i=0;$i<count($datas);$i++)
 		{
	 		$key=$i+2;
	 		$objActSheet->setCellValue('A'.$key, $datas[$i]['sku']);
	 		$objActSheet->setCellValue('B'.$key, $datas[$i]['sku_name']);
			$objActSheet->setCellValue('C'.$key, $datas[$i]['sku_standard']);
	 		$objActSheet->setCellValue('D'.$key, $datas[$i]['storage_position']);
	 		$objActSheet->setCellValue('E'.$key, $datas[$i]['supplier']);
	 		$objActSheet->setCellValue('F'.$key, $datas[$i]['sum(b.quantity)']);
	 		$objActSheet->setCellValue('G'.$key, $datas[$i]['single_price']);
	 		$objActSheet->setCellValue('H'.$key, $datas[$i]['sum(b.no_tax_money)']);
	 		$objActSheet->setCellValue('I'.$key, $datas[$i]['sum(b.tax_money)']);
	 		$objActSheet->setCellValue('J'.$key, $datas[$i]['sum(b.money)']);
	 		$objActSheet->setCellValue('K'.$key, $datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('L'.$key, $datas[$i]['sum(b.no_tax_money)']+$datas[$i]['sum(b.transportation_expense)']);
	 		$objActSheet->setCellValue('M'.$key, $datas[$i]['sum(b.recieve_quantity)']);
	 		$objActSheet->setCellValue('N'.$key, $datas[$i]['sum(b.check_quantity)']);
	 		$objActSheet->setCellValue('O'.$key, $datas[$i]['sum(b.ware_quantity)']);
	 		$objActSheet->setCellValue('P'.$key, $datas[$i]['no_recieve']);
	 		$objActSheet->setCellValue('Q'.$key, $datas[$i]['extra_recieve_quantity']);
 		}
 		$objPHPExcel->getActiveSheet()->setTitle('采购订单报表');
 		$objPHPExcel->setActiveSheetIndex(0);
 		$day=date("m-d");
 		$filename=$day.'采购订单报表(common).xls';
        ob_end_clean();
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename='.$filename);
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 		$objWriter->save('php://output');
 		exit;
	}
}
